package com.shuhe.domain.mxgc;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.shuhe.common.CommonFunction;
import com.shuhe.common.MyDateUtils;
import com.shuhe.metastore.DBSql;
import com.shuhe.metastore.MetaMysql;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import static com.shuhe.common.Base64.byteConvertBase64;
import static com.shuhe.common.CommonFunction.*;
import static com.shuhe.common.MyDateUtils.getDataString;
import static com.shuhe.common.MyDateUtils.getNowDate;
import static com.shuhe.handler.ServiceDetail.dealdot;

public class ExeclOperate {

    private static String getCellValue(Sheet sheet, int rowNumber, int columnNumber) {
        Row row = sheet.getRow(rowNumber);
        if (row == null) return null;
        Cell cell = row.getCell(1);
        if (cell != null) {
            if (cell.getCellType() == CellType.STRING) {
                String value = cell.getStringCellValue();
                if (value.equals("未匹配到数据")) {
                    return "next";
                }
            }
        }
        cell = row.getCell(columnNumber);

        if (cell != null) {
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return getDataString(cell.getDateCellValue());
                    } else {
                        return String.valueOf(cell.getNumericCellValue());
                    }
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case FORMULA:
                    return cell.getCellFormula();
                default:
                    return "";
            }
        } else {
            return "";
        }
    }


    private static String getXlsImage(Sheet sheet, int rowNumber, int columnNumber) {
        // 获取绘图对象
        HSSFPatriarch patriarch = (HSSFPatriarch) sheet.getDrawingPatriarch();
        if (patriarch != null) {
            // 获取所有图片
            List<HSSFShape> shapes = patriarch.getChildren();
            for (HSSFShape shape : shapes) {
                if (shape instanceof HSSFPicture) {
                    HSSFPicture picture = (HSSFPicture) shape;
                    ClientAnchor anchor = (ClientAnchor) picture.getAnchor();

                    // 获取图片的位置信息
                    int col1 = anchor.getCol1();
                    int row1 = anchor.getRow1();
                    int col2 = anchor.getCol2();
                    int row2 = anchor.getRow2();

                    // 获取图片数据
                    HSSFPictureData pictureData = picture.getPictureData();
                    if (col1 == columnNumber && row1 == rowNumber) {
                        return byteConvertBase64(pictureData.getData());
                    }
                }
            }
        }
        return "";
    }

    private static int oneSheet_oneTable(Connection metaConn, Connection dbConn, Workbook workbook,
                                         JSONObject template_json, String coverInsert, String userName,
                                         String execl_import_time) throws Exception {
        int insertRecordNumber = 0;
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取工作表
            String sheetName = sheet.getSheetName();
            JSONArray jsonSheets = template_json.getJSONArray("sheets");
            for (int k = 0; k < jsonSheets.size(); k++) {
                JSONObject jsonSheet = jsonSheets.getJSONObject(k);
                String sheetName2 = jsonSheet.getString("sheet_name");
                if (sheetName.equals(sheetName2)) {
                    //获取表名
                    String tableName = jsonSheet.getString("table_name");
                    String table_type = jsonSheet.getString("table_type");
                    JSONArray jsonColumns = jsonSheet.getJSONArray("cols");
                    //获取字段名
                    JSONObject jsonSql = makeCreateAndInsert(metaConn, jsonSheet, tableName, userName);
                    String create_sql = jsonSql.getString("create_sql");
                    String insert_sql = jsonSql.getString("insert_sql");

                    //是否重新建立目标表
                    if (coverInsert.equals("cover")) {
                        String delete_sql = "delete from " + tableName;
                        MetaMysql.execSql(dbConn, delete_sql);
                        //MetaMysql.execSql(dbConn, create_sql);
                    }
                    //设置表接入类型
                    String update_sql =
                            "update standard_table set bz1='template' where table_name='" + dealdot(tableName)
                            + "' and table_type = 'original'";
                    MetaMysql.execSql(metaConn, update_sql);
                    //插入数据
                    ArrayList insertSqlS = new ArrayList();
                    if (table_type.equals("one_record")) {
                        jsonColumns = addColumn(jsonColumns);
                        for (int n = 0; n < jsonColumns.size(); n++) {
                            JSONObject jsonColumn = jsonColumns.getJSONObject(n);
                            String rowIndex = jsonColumn.getString("rowIndex");
                            rowIndex = dealNull(rowIndex, "0");
                            String value = getJsonValue(sheet, Integer.parseInt(rowIndex), jsonColumn,
                                    execl_import_time);
                            insert_sql = insert_sql + value + "','";
                        }
                        insert_sql = insert_sql.substring(0, insert_sql.length() - 2);
                        insert_sql = insert_sql + ")";
                        insertSqlS.add(insert_sql);
                    }
                    if (table_type.equals("multi_record")) {
                        int rowCount = sheet.getLastRowNum() + 1;
                        for (int m = 1; m < rowCount; m++) {
                            String sql = makeInsertSql(jsonSheet, insert_sql, sheet, m, execl_import_time);
                            if (sql.length() > 0) {
                                insertSqlS.add(sql);
                            }
                        }
                    }
                    insertRecordNumber = insertRecordNumber + insertSqlS.size();
                    MetaMysql.batchExecSql(dbConn, insertSqlS);
                }
            }
        }
        return insertRecordNumber;
    }

    private static JSONArray addColumn(JSONArray jsonColumns) {
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("col_e_name", "execl_import_time");
        jsonObject.put("col_c_name", "EXECL导入时间");
        jsonObject.put("col_type", "varchar");
        jsonObject.put("col_length", "30");
        boolean isExist = false;
        for (int i = 0; i < jsonColumns.size(); i++) {
            JSONObject jsonColumn = jsonColumns.getJSONObject(i);
            String col_e_name = jsonColumn.getString("col_e_name");
            if (col_e_name.equals("execl_import_time")) {
                isExist = true;
            }
        }
        if (!isExist) {
            jsonColumns.add(jsonObject);
        }
        return jsonColumns;
    }

    private static JSONObject makeCreateAndInsert(Connection metaConn, JSONObject jsonTable, String tableName,
                                                  String userName) throws Exception {
        JSONArray jsonColumns = jsonTable.getJSONArray("cols");
        jsonColumns = addColumn(jsonColumns);
        String tableComment = jsonTable.getString("sheet_name");
        if (tableComment == null) {
            tableComment = jsonTable.getString("table_comment");
        }
        String db_name = tableName.split("\\.")[0];
        String only_table_name = tableName.split("\\.")[1];
        String insert_sql = "insert into " + tableName + "(";
        String create_table_sql = "CREATE TABLE " + tableName + "(";

        //生成元数据
        MetaMysql.execSql(metaConn,
                "delete from mxgc_user_table where db_name = '" + db_name + "' and table_name = " + "'" + only_table_name + "'");
        String mxgc_user_table_sql = "insert into mxgc_user_table(id, user_name, db_name, table_name, table_comment, "
                + "createtime, table_type) values('" + CommonFunction.getUUID() + "', '" + userName + "', '" + db_name + "', '" + only_table_name + "', '" + tableComment + "', '" + getNowDate() + "', '1')";
        MetaMysql.execSql(metaConn, mxgc_user_table_sql);
        String mxgc_user_column_sql = "insert into mxgc_user_column(db_name, table_name, column_name, column_comment,"
                + "ordinal_position, data_type) values(";
        ArrayList<String> column_sqlS = new ArrayList();
        column_sqlS.add("delete from mxgc_user_column where db_name = '" + db_name + "' and table_name = '" + only_table_name + "'");
        for (int m = 0; m < jsonColumns.size(); m++) {
            JSONObject jsonColumn = jsonColumns.getJSONObject(m);
            String col_e_name = jsonColumn.getString("col_e_name");
            String col_c_name = jsonColumn.getString("col_c_name");
            String col_type = jsonColumn.getString("col_type");
            String col_length = jsonColumn.getString("col_length");
            if (col_type.equals("text")) {
                create_table_sql = create_table_sql + col_e_name + " " + col_type + ",";
            } else {
                create_table_sql = create_table_sql + col_e_name + " " + col_type + "(" + col_length + ") " + ",";
            }
            insert_sql = insert_sql + col_e_name + ",";

            String sql = mxgc_user_column_sql + "'" + db_name + "', '" + only_table_name + "', '" + col_e_name + "', "
                    + "'" + col_c_name + "', " + m + ", 'varchar')";
            column_sqlS.add(sql);
        }
        //生成元数据
        MetaMysql.batchExecSql(metaConn, column_sqlS);
        create_table_sql = create_table_sql.substring(0, create_table_sql.length() - 1);
        create_table_sql = create_table_sql + ")";
        insert_sql = insert_sql.substring(0, insert_sql.length() - 1);
        insert_sql = insert_sql + ") values('";
        JSONObject json = new JSONObject();
        json.put("create_sql", create_table_sql);
        json.put("insert_sql", insert_sql);
        return json;
    }

    private static String getJsonValue(Sheet sheet, int m, JSONObject jsonColumn, String execl_import_time) {
        String col_e_name = jsonColumn.getString("col_e_name");
        String colIndex = jsonColumn.getString("colIndex");
        String col_type = jsonColumn.getString("col_type");
        String value = "";
        if (col_e_name.equals("execl_import_time")) {
            value = execl_import_time;
        } else {
            value = getCellValue(sheet, m, Integer.parseInt(colIndex));
            if (value == null) {
                return null;
            }
            if (col_type.equals("text")) {
                //获取图片
                value = getXlsImage(sheet, m, Integer.parseInt(colIndex));
            }
        }
        value = value.replaceAll("'", "");
        return value;
    }

    private static String makeInsertSql(JSONObject jsonTable, String insert_sql, Sheet sheet, int m,
                                        String execl_import_time) {
        JSONArray jsonColumns = jsonTable.getJSONArray("cols");
        jsonColumns = addColumn(jsonColumns);
        String sql = insert_sql;
        int nullNumber = 0;
        for (int n = 0; n < jsonColumns.size(); n++) {
            JSONObject jsonColumn = jsonColumns.getJSONObject(n);
            String value = getJsonValue(sheet, m, jsonColumn, execl_import_time);
            if (value == null || value.equals("未匹配到数据")) {
                return "";
            }
            sql = sql + value + "','";
            if (value.length() == 0) {
                nullNumber++;
            }
        }
        if (nullNumber == jsonColumns.size() - 1) {
            //所有字段都为空
            return "";
        }
        sql = sql.substring(0, sql.length() - 2);
        sql = sql + ")";
        return sql;
    }

    private static int oneSheet_mulTable(Connection metaConn, Connection dbConn, Workbook workbook,
                                         JSONObject template_json, String coverInsert, String userName,
                                         String execl_import_time) throws Exception {
        Sheet sheet = workbook.getSheetAt(0); // 获取工作表
        JSONArray jsonTables = template_json.getJSONArray("tables");
        int rowCount = sheet.getLastRowNum() + 1;
        int m = 0;
        ArrayList<String> createTables = new ArrayList();
        int insertRecordNumber = 0;
        while (m < rowCount) {
            boolean isFound = false;
            for (int i = 0; i < jsonTables.size(); i++) {
                JSONObject jsonTable = jsonTables.getJSONObject(i);
                String identifierString = jsonTable.getString("identifierString");
                String tableName = jsonTable.getString("table_name");
                int identifierColIndex = jsonTable.getInteger("identifierColIndex");
                String value = getCellValue(sheet, m, identifierColIndex);
                if (identifierString.equals(value)) {
                    isFound = true;
                    //找到表头 获取字段名
                    JSONObject jsonSql = makeCreateAndInsert(metaConn, jsonTable, tableName, userName);
                    String create_sql = jsonSql.getString("create_sql");
                    String insert_sql = jsonSql.getString("insert_sql");

                    //是否重新建立目标表, 并判断是否已经创建过
                    if (coverInsert.equals("cover") && createTables.indexOf(tableName) < 0) {
                        createTables.add(tableName);
                        String delete_sql = "delete from " + tableName;
                        MetaMysql.execSql(dbConn, delete_sql);
                        //MetaMysql.execSql(dbConn, create_sql);
                    }

                    //设置表接入类型
                    String update_sql = "update standard_table set bz1='template' where table_name='" + dealdot(tableName)
                            + "' and table_type = 'original'";
                    MetaMysql.execSql(metaConn, update_sql);
                    //数据处理
                    ArrayList<String> insertSqlS = new ArrayList();
                    while (m < rowCount) {
                        m = m + 1;
                        //插入数据
                        String sql = makeInsertSql(jsonTable, insert_sql, sheet, m, execl_import_time);
                        if (sql.length() > 0) {
                            insertSqlS.add(sql);
                        }
                        //获取下张表标识符
                        JSONObject jsonNextTable = new JSONObject();
                        if (i + 1 < jsonTables.size()) {
                            jsonNextTable = jsonTables.getJSONObject(i + 1);
                        } else {
                            jsonNextTable = jsonTables.getJSONObject(0);
                        }
                        identifierString = jsonNextTable.getString("identifierString");
                        identifierColIndex = jsonNextTable.getInteger("identifierColIndex");
                        value = getCellValue(sheet, m + 1, identifierColIndex);
                        if (value == null) {
                            continue;
                        }
                        if (identifierString.equals(value) || value.equals("next")) {
                            insertRecordNumber = insertRecordNumber + insertSqlS.size();
                            MetaMysql.batchExecSql(dbConn, insertSqlS);
                            m = m + 1;
                            break;
                        }
                    }
                }
            }
            if (!isFound) {
                m = m + 1;
            }
        }
        return insertRecordNumber;
    }

    public static String importEXECL(JSONObject bodyJson) throws Exception {

        //获取数据
        Workbook workbook = null;
        FileInputStream fis = null;
        Connection dbConn = null;
        Connection metaConn = null;
        String execl_import_time = MyDateUtils.getNowDate();
        int insertRecoredNumber = 0;
        try {
            dbConn = DBSql.connect();
            metaConn = MetaMysql.connect();
            JSONArray file_list = bodyJson.getJSONArray("file_list");
            for (int j = 0; j < file_list.size(); j++) {
                String name = file_list.getString(j);
                String userName = bodyJson.getString("userName");
                String coverInsert = bodyJson.getString("coverInsert");
                String filePath = System.getProperty("user.dir");
                filePath = filePath + File.separator + userName + '_' + name;
                JSONObject template_json = bodyJson.getJSONObject("template_json");
                fis = new FileInputStream(new File(filePath));
                workbook = getWorkbook(fis, filePath);
                if (template_json.getString("workBookType").equals("oneSheet_oneTable")) {
                    //一个工作表对应一个表
                    int cun = oneSheet_oneTable(metaConn, dbConn, workbook, template_json, coverInsert, userName,
                            execl_import_time);
                    insertRecoredNumber = insertRecoredNumber + cun;
                }
                if (template_json.getString("workBookType").equals("oneSheet_mulTable")) {
                    //一个工作表对应多个表
                    int cun = oneSheet_mulTable(metaConn, dbConn, workbook, template_json, coverInsert, userName,
                            execl_import_time);
                    insertRecoredNumber = insertRecoredNumber + cun;
                }
            }

            CommonResponseBody resp = new CommonResponseBody();
            resp.setSuccess();
            resp.setMsg("成功导入" + insertRecoredNumber + "条数据");
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
            try {
                workbook.close();
                fis.close();
            } catch (Exception e) {
            }
        }
    }


    private static Workbook getWorkbook(FileInputStream fis, String filePath) throws Exception {
        String fileExtension = getFileExtension(filePath);

        switch (fileExtension) {
            case "xls":
                return new HSSFWorkbook(fis);
            case "xlsx":
                return new XSSFWorkbook(fis);
            default:
                return null;
        }
    }

    private static String getFileExtension(String filePath) {
        int lastIndexOfDot = filePath.lastIndexOf('.');
        if (lastIndexOfDot == -1) {
            return ""; // 没有扩展名
        }
        return filePath.substring(lastIndexOfDot + 1).toLowerCase();
    }


}
